import user.zc.excel.render.ExcelRender;
import user.zc.file.WriteFile;
import user.zc.helper.ExcelAssistant;
import user.zc.helper.ExcelWriteUtil;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhouchuang
 * @date 2024-10-11 12:39:01
 */
public class TableInfoExportUtil {

    static final String url = "jdbc:mysql://localhost:3306/nacos";
    static final String user = "root";
    static final String password = "123456";

    public static void main(String[] args) throws Exception{
        List<TableInfo> collection = collection();
        ExcelWriteUtil.builder()
                .create(new ExcelRender(collection,"nacos数据库表信息"))
                //如果是浏览器输出 outputResource传入 new ResponseFile(HttpServletResponse,String);
                .outputResource(new WriteFile("C:\\Users\\zhouc\\Desktop\\table.xls"))
                .build().general();
    }

    public static List<TableInfo> collection()throws Exception{
        List<TableInfo> list  = new ArrayList<>();
        int i = 0;
        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                String remark = tables.getString("REMARKS");


                ResultSet columns = metaData.getColumns(null, null, tableName, null);

                while (columns.next()) {
                    String columnName = columns.getString("COLUMN_NAME"); // 列名
                    String columnType = columns.getString("TYPE_NAME"); // 数据类型
                    String columnSize = columns.getString("COLUMN_SIZE"); // 列大小
                    String columnRemarks = columns.getString("REMARKS"); // 列注释
                    boolean isNullable = columns.getInt("NULLABLE") == DatabaseMetaData.columnNullable; // 是否可为空

                    TableInfo tableInfo = new TableInfo();
                    tableInfo.setEnName(tableName+"\n"+remark);
                    tableInfo.setColumnRemark(columnRemarks);
                    tableInfo.setColumnDateType(columnType);
                    tableInfo.setColumnName(columnName);
                    list.add(tableInfo);
                }
                columns.close();
            }
            tables.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
